Attribute VB_Name = "ASROutputFile"
'**WRITTEN BY JALISA GREENE**'
'**LAST MODIFIED BY JALISA GREENE**'
'**LAST MODIFIED 6/5/2014**'


Public same As Boolean
Public v1, v2, v3, v4, v5, v6, v7, v8 As Boolean '**VENDOR LOGIC VARIABLES -- DO WE HAVE THIS VENDOR?**'
Public r1, r2, r3, r4, r5, r6, r7, r8 As Boolean '**RATE REDUCTION LOGIC VARIABLES -- DO WE HAVE A RATE REDUCTION?**'
Public c1, c2, c3, c4, c5, c6, c7, c8 As Boolean '**BILLING CORRECTION LOGIC VARIABLES -- DO WE HAVE A BILLING CORRECTION?**'
Public s1, s2, s3, s4, s5, s6, s7, s8 As Boolean '**SERVICE STANDARDIZATION LOGIC VARIABLES -- DO WE HAVE A SERVICE STANDARDIZATION?**'
Public vs1, vs2, vs3, vs4, vs5, vs6, vs7, vs8 As Boolean ''**VENDOR SWITCH LOGIC VARIABLES -- DO WE HAVE A VENDOR SWITCH?**'
Dim lastLoc As Integer '**LAST LOCATION IN THE SHEET**'
Dim Col As Long '**COLOR TO SHADE WITH**'
Dim eof As Range '**END OF FILE**'


Sub GenerateOutput()
'**GENERATE OUTPUT FILE MACRO THAT CREATES THE ASR OUTPUT FILE - <DATE> AS WELL AS THE ASR OUTPUT SUMMARY TABS**'

Dim msg As Integer
Dim lastRow As Integer


msg = MsgBox("This file will take approximately 10 full minutes to generate.During this time you will not be able to access anyting in Microsoft Excel. Please be patient while your Output File is being generated...Click OK to begin.", vbOKCancel)
If msg = 1 Then '**THE USER CLICKED OKAY TO BEGIN THE ASR GENERATION**'
    Application.ScreenUpdating = False
    '**FIRST CLEAR EVERYTHING IN ASR OUTPUT SUMMARY AND ASR OUTPUT FILE**'
    Worksheets("ASR Output Summary").Activate
    ActiveSheet.Rows("1:100").ClearContents
    ActiveSheet.Rows("1:100").ClearFormats
    Worksheets("ASR Output File").Visible = True
    Worksheets("ASR Output File").Activate
    ActiveSheet.Rows("14:4000").ClearContents
    ActiveSheet.Rows("14:4000").ClearFormats
    Range("A14").Select
    lastRow = Worksheets("ASR").Range("A1099").End(xlUp).Row + 1
    
    '**COPY LOCATION ID VALUES FROM THE ASR**'
    Dim loc As Range
    '**FIRST COPY THE LOCATIONS WE HAVE VENDOR INFORMATION FOR -- ONES WE ARE AFFECTING**'
    For Each loc In Worksheets("ASR").Range("A8", "A" & lastRow)
        If loc.Value <> "" And (Worksheets("ASR").Range("AA" & loc.Row).Value <> "" Or Worksheets("ASR").Range("KA" & loc.Row).Value <> "") Then
             ActiveCell.Value = loc.Value
             Range("AQ" & ActiveCell.Row).Value = loc.Value
             ActiveCell.Offset(1).Select
        End If
    Next
    
    Range("A:A").EntireColumn.NumberFormat = "000000"
    '**SORT, ASCENDING**'
    Range("A14: A" & ActiveCell.Row).Sort key1:=Range("A14")
    '**COPY THE LOCATIONS WE DON'T HAVE ANY INFORMATION FOR -- ONES WE ARE NOT AFFECTING**'
    For Each loc In Worksheets("ASR").Range("A8", "A" & lastRow)
        If loc.Value <> "" And Worksheets("ASR").Range("AA" & loc.Row).Value = "" And Worksheets("ASR").Range("KA" & loc.Row).Value = "" Then
             ActiveCell.Value = loc.Value
             Range("AQ" & ActiveCell.Row).Value = loc.Value
             ActiveCell.Offset(1).Select
        End If
    Next
    
    '**INSERT EXTRA ROWS BETWEEN EACH LOCATION ID, AS WELL AS ADDRESS AND BASELINE DATE**'
    Call InsertRows
    '**INSERT CURRENT SPEND VENDOR NAME AND ACCT NUMBERS**'
    Call VendorNames
    '**INSERT CURRENT AND ACHIEVED SERVICE INFORMATION**'
    Call InsertServices
    '**INSERT ACHIEVED SAVINGS COLUMNS**'
    Call InsertSavings
    '**INSERT IN IMPLEMENTED CONTRACT INFORMATION**;
    Call InsertNewContracts
    '**INSERT SUBTOTALS FOR EACH ACCOUNT**'
    Call InsertSubtotals
    '**REMOVE UNNECCESSARY BLANK LINES**'
    Call RemoveSpaces
    Call RemoveSpaces
    '**INSERT LOCATION TOTALS**'
    Call InsertLocTotal
    '**REMOVE UNNECCESSARY BLANK LINES**'
    Call RemoveSpaceAfterTotals
    
    
    'FINAL FORMATTING'
    With Worksheets("ASR Output File").Cells.Font
        .Name = "Helvetica"
        .Size = 12
    End With
    With Worksheets("ASR Output File").Rows
        .RowHeight = .RowHeight * 1.2
    End With
    
    Range("AQ:AQ").EntireColumn.HorizontalAlignment = xlLeft
    Range("B:D").EntireColumn.HorizontalAlignment = xlCenter
    Range("E:E").EntireColumn.HorizontalAlignment = xlRight
    Range("H:H").EntireColumn.ClearFormats
    Range("N:N").EntireColumn.ClearFormats
    Range("S:S").EntireColumn.ClearFormats
    Range("X:X").EntireColumn.ClearFormats
    Range("AD:AD").EntireColumn.ClearFormats
    Range("AI:AI").EntireColumn.ClearFormats
    Range("AP:AP").EntireColumn.ClearFormats
    Range("AW:AW").EntireColumn.ClearFormats
    Range("BH:BH ").EntireColumn.ClearFormats
    Range("BN:BN").EntireColumn.ClearFormats
    Range("BU:BU").EntireColumn.ClearFormats
    Range("BR:BR").EntireColumn.Clear
    Range("CC:CC").EntireColumn.ClearFormats
    Range("F:G").EntireColumn.NumberFormat = "0.0%"
    Range("L:L").EntireColumn.NumberFormat = "0.0%"
    Range("Q:Q").EntireColumn.NumberFormat = "0.0%"
    Range("V:V").EntireColumn.NumberFormat = "0.0%"
    Range("AB:AB").EntireColumn.NumberFormat = "0.0%"
    Range("AG:AG").EntireColumn.NumberFormat = "0.0%"
    Range("AU:AU").EntireColumn.NumberFormat = "0.0%"
    Range("C:D").EntireColumn.HorizontalAlignment = xlCenter
    Range("F:G").EntireColumn.HorizontalAlignment = xlCenter
    Range("A13").EntireRow.ClearContents
    Range("A5").Font.Size = 20
    ActiveCell.EntireRow.RowHeight = ActiveCell.RowHeight * 1.2
    
    '**ADD DBA TO LOCATION ID**'
    Call ModifyLocName
    
    '**REMOVE UNNECCESSARY BLANK LINES**
    Call RemoveSpaceAfterTotals
    Call RemoveSpaceAfterTotals
    Range("A14").Select
    
    '**MAKE DATED COPY READY FOR EDITING**'
    Worksheets("ASR Output File").Copy After:=Worksheets("API Calculation")
    ActiveSheet.Name = "ASR Output File " & Format(Date, "mm-dd-yyyy")
    Worksheets("ASR Output File").Visible = False
    Application.ScreenUpdating = True
    
    '**AUTO-HIDE ACHIEVED SAVINGS THAT = $0**'
    msg = MsgBox("Would you like to auto-hide Achieved Savings columns that = $0? ", vbYesNo)
    If msg = 6 Then 'the user clicked Yes
        If Worksheets("ASR").Range("V1100").Value = 0 Then
            ActiveSheet.Columns("O:S").Hidden = True
            ActiveSheet.Columns("AJ:AJ").Hidden = True
        End If
        If Worksheets("ASR").Range("W1100").Value = 0 Then
            ActiveSheet.Columns("T:X").Hidden = True
            ActiveSheet.Columns("AK:AK").Hidden = True
        End If
        If Worksheets("ASR").Range("W1100").Value = 0 Then
            ActiveSheet.Columns("Y:AD").Hidden = True
            ActiveSheet.Columns("AL:AL").Hidden = True
        End If
        If Worksheets("ASR").Range("W1100").Value = 0 Then
            ActiveSheet.Columns("AE:AI").Hidden = True
            ActiveSheet.Columns("AM:AM").Hidden = True
        End If
    End If

End If

End Sub

Private Sub InsertRows()
'** THIS SUB IS CALLED FROM THE MAIN RUNNING METHOD, INSERTS SOME BLANK LINES BETWEEN EACH LOCATION, AS WELL AS THE ADDRESS AND BASELINE SPEND INV DATE**'


Dim loc As Range '**REPRESENTS LOCATION ID**'
Dim countRows As Integer '**COUNTS ROWS SO WE HAVE ENOUGH IN BETWEEN THE CURRENT AND NEXT LOCATION**'
Dim Vendor1Lookup As String
Dim Vendor2Lookup As String
Dim Vendor3Lookup As String
Dim Vendor4Lookup As String
Dim Vendor5Lookup As String
Dim Vendor6Lookup As String
Dim Vendor7Lookup As String
Dim Vendor8Lookup As String
Dim rowNum As Integer '**MATCHED ROW NUMBER TO ACCESS INFO IN THE ASR**'
Dim lastRow As Integer '**LAST USED ROW IN THE SHEET**'


'**INSERT BLANK ROWS BETWEEN THE CURRENT LOCATION AND THE NEXT, BASED ON IF WE HAVE VENDOR 1 W/ FEES**'
lastRow = Range("A4000").End(xlUp).Row
For Each loc In Range("A14", "A" & lastRow)
    If TypeName(loc.Value) = "Double" Then
        loc.Select
        rowNum = WorksheetFunction.Match(loc, Worksheets("INV").Range("locations"), 0) + 7
        If WorksheetFunction.VLookup(loc.Value, Worksheets("ASR").Range("A8:DB1100"), 27, False) <> "" Then
            Vendor1Lookup = WorksheetFunction.VLookup(loc.Value, Worksheets("ASR").Range("A8:DB1100"), 27, False)
            If Worksheets("ASR").Range("AP" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("AQ" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("AS" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("AU" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("AW" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("AY" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("BA" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
        End If
        If WorksheetFunction.VLookup(loc.Value, Worksheets("ASR").Range("A8:DB1100"), 79, False) <> "" Then
            Vendor2Lookup = WorksheetFunction.VLookup(loc.Value, Worksheets("ASR").Range("A8:DB1100"), 79, False)
            If Worksheets("ASR").Range("CP" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("CQ" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("CS" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("CU" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("CW" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("CY" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("DA" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
        End If
        If WorksheetFunction.VLookup(loc.Value, Worksheets("ASR").Range("A8:JB1100"), 131, False) <> "" Then
            Vendor3Lookup = WorksheetFunction.VLookup(loc.Value, Worksheets("ASR").Range("A8:PZ1100"), 131, False)
            If Worksheets("ASR").Range("EP" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("EQ" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("ES" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("EU" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("EW" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("EY" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
            If Worksheets("ASR").Range("FA" & rowNum) <> "" Then ActiveCell.Offset(1).EntireRow.Insert
        End If
    End If
Next

'**INSERT ADDRESS AND BASELINE SPEND INVOICE DATE**'
lastRow = Range("A4000").End(xlUp).Row
For Each loc In Range("A14", "A" & lastRow)
    If TypeName(loc.Value) = "Double" Then
        loc.Select
        '**find the location's row number in the Inventory**'
        rowNum = WorksheetFunction.Match(loc, Worksheets("INV").Range("locations"), 0) + 7
        '**insert blank space if needed**'
        If ActiveCell.Offset(1).Value <> "" Then ActiveCell.Offset(1).EntireRow.Insert
        ActiveCell.Offset(1).Select
        '**if we have street number and street, insert it in activecell**'
        If Worksheets("ASR").Range("C" & rowNum).Value <> 0 Then ActiveCell.Value = Worksheets("ASR").Range("C" & rowNum).Value
        '**insert the same thing in the implemented spend location id column**'
        Range("AQ" & ActiveCell.Row).Value = ActiveCell.Value
        If ActiveCell.Offset(1).Value <> "" Then ActiveCell.Offset(1).EntireRow.Insert          '**insert space if necessary**'
        ActiveCell.Offset(1).Select
        '**if we have city state zip, insert in activecell**'
        If Worksheets("ASR").Range("D" & rowNum).Value <> 0 Then ActiveCell.Value = Worksheets("ASR").Range("K" & rowNum).Value
        '**insert the same thing in the implemented spend location id column**'
        Range("AQ" & ActiveCell.Row).Value = ActiveCell.Value
        '**Baseline spend invoice date**'
        ActiveCell.Offset(-2, 2).Value = Format(Worksheets("ASR").Range("AL" & rowNum).Value, "mm/dd/yyyy")
    End If
Next

'**INSERT IN SOME EXTRA BLANKS BETWEEN EACH LOCATION**'
lastRow = Range("A4000").End(xlUp).Row
For Each loc In Range("A14", "A" & lastRow)
    If TypeName(loc.Value) = "Double" Then
        loc.Select
        '**LEFT ALIGN THE LOCATION ID'
        ActiveCell.HorizontalAlignment = xlLeft
        Range("AQ" & ActiveCell.Row).HorizontalAlignment = xlLeft
        '**INSERT 6 BELOW**'
        ActiveCell.Offset(3).EntireRow.Insert
        ActiveCell.Offset(3).EntireRow.Insert
        ActiveCell.Offset(3).EntireRow.Insert
        ActiveCell.Offset(3).EntireRow.Insert
        ActiveCell.Offset(3).EntireRow.Insert
        ActiveCell.Offset(3).EntireRow.Insert
    End If
Next
End Sub

Private Sub VendorNames()
'** THIS SUB IS CALLED FROM THE MAIN RUNNING METHOD, INSERTS IN VENDOR NAMES AND ACCOUNT NUMBERS FOR EACH CONTAINER AT EACH LOCATION**'
'** EACH LOCATION COULD HAVE AN UPWARDS OF 8 CONTAINERS. FOR EACH OF THE REGULAR 5 CONTAINERS WE SKIP 10 LINES, FOR THE 3 COMPACTORS WE SKIP 20 LINES.**'

Dim loc As Range
Dim rowNum As Integer
Dim count As Integer
Dim contract As String


Worksheets("ASR Output File").Activate
'INSERT VENDOR NAMES AND ACCOUNT NUMBERS'
For Each loc In Range("locationsOUT")
    If TypeName(loc.Value) = "Double" Then
        loc.Select
        rowNum = WorksheetFunction.Match(loc, Worksheets("INV").Range("locations"), 0) + 7
        '**INSERT VENDOR FOR CONTAINER 1 IF WE HAVE ONE**'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 27, False) <> "" Then
            Range("I" & ActiveCell.Row).Select
             ActiveCell.Value = Worksheets("ASR").Range("AA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            ActiveCell.Value = Worksheets("ASR").Range("AB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            '**INSERT UNDERLYING HAULER FOR VENDOR 1**'
            If Worksheets("ASR").Range("BL" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("BL" & rowNum).Value
            '**INSER CONTRACT IF EXISTS FOR VENDOR 1**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 57, False)
                If contract <> "" Then
                '**IF THE LEN OF CONTRACT IS > 15 THEN WE ASSOCIATED A REAL CONTRACT, ELSE WE TYPED IN NO CONTRACT OR SOMETHING SIMILAR**'
                    If Len(contract) > 15 Then
                        Range("B" & ActiveCell.Offset(-1).Row).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("F" & ActiveCell.Offset(-1).Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        Range("B" & ActiveCell.Offset(-1).Row).Value = contract
                    End If
                End If
                ActiveCell.Offset(1).Select
                
        End If
        
        '**INSERT VENDOR 2 INFORMATION IF EXISTS'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 79, False) <> "" Then
            '**IF WE STILL HAVE THE LOCATION ID SELECTED, GO OVER TO VENDOR COLUMN'
            If TypeName(ActiveCell.Value) = "Double" Then
                ActiveCell.Offset(0, 8).Select
            'else go down one row because we already inserted one vendor'
            Else: ActiveCell.Offset(1).Select
            End If
            '**IF THERE IS NOTHING BESIDE THE ACTIVE CELL(because we already have 1 vendor) INSERT 10 ROWS**'
            If ActiveCell.Offset(0, -8).Value = "" Then
                count = 1
                While count < 10
                    ActiveCell.EntireRow.Insert
                    count = count + 1
                Wend
                '**GO DOWN TO WHERE WE SHOULD ENTER IN THE 2ND VENDOR INFO**'
                ActiveCell.Offset(8).Select
            End If
            '**INSERT VENDOR NAME**'
            ActiveCell.Value = Worksheets("ASR").Range("CA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            '**IF WE HAVE INFO IN THE LOCATION ID CELL, WE ARE TOO FAR DOWN AND NEED TO INSERT A ROW**'
            If TypeName(ActiveCell.Offset(0, -8).Value) = "Double" Then ActiveCell.EntireRow.Insert
            '**INSERT SOME EMPTY SPACE**'
            If ActiveCell.Offset(0, -8).Value = "" Then
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
            End If
            '**ACCT NUM**'
            ActiveCell.Value = Worksheets("ASR").Range("CB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            If Worksheets("ASR").Range("DL" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("DL" & rowNum).Value
            Range("B" & ActiveCell.Offset(-1).Row).Select
            '**CURRENT CONTRACT EXPIRATION'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 109, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        ActiveCell.Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("F" & ActiveCell.Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
        ActiveCell.Offset(2, 7).Select
        End If
        
        
        'INSERT CONTAINER 3 IF EXISTS
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 131, False) <> "" Then
            'IF ACTIVE CELL IS STILL LOCATION ID OFFSET TO VENDOR COLUMN, ELSE OFFSET 1 ROW"
            If TypeName(ActiveCell.Value) = "Double" Then
                ActiveCell.Offset(0, 8).Select
            Else: ActiveCell.Offset(1).Select
            End If
            'if we have nothing beside the active cell we have a 1st vendor, add 10 rows'
            If ActiveCell.Offset(0, -8).Value = "" Then
                count = 0
                While count < 10
                    ActiveCell.EntireRow.Insert
                    count = count + 1
                Wend
                ActiveCell.Offset(8).Select
            End If
            '**VENDOR 3 NAME**'
            ActiveCell.Value = Worksheets("ASR").Range("EA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            If TypeName(ActiveCell.Offset(0, -8).Value) = "Double" Then ActiveCell.EntireRow.Insert
            '**EMPTY SPACE**'
            If ActiveCell.Offset(0, -8).Value = "" Then
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
            End If
            '**ACCT NUM**'
            ActiveCell.Value = Worksheets("ASR").Range("EB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            '**UNDERLYING HAULER**'
            If Worksheets("ASR").Range("HL" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("HL" & rowNum).Value
            Range("B" & ActiveCell.Offset(-1).Row).Select
            '**CURRENT CONTRACT EXPIRATION**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 161, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        ActiveCell.Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("E" & ActiveCell.Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
            ActiveCell.Offset(2, 7).Select
        End If
        
        
        '**VENDOR 4 IF EXISTS**'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 183, False) <> "" Then
            '**IF ACTIVE CELL IS STILL LOCATION ID OFFSET TO VENDOR COLUMN, ELSE OFFSET 1 ROW**'
            If TypeName(ActiveCell.Value) = "Double" Then
                ActiveCell.Offset(0, 8).Select
            Else: ActiveCell.Offset(1).Select
            End If
            '**ADD 10 ROWS FOR SPACE
            If ActiveCell.Offset(0, -8).Value = "" Then
                count = 0
                While count < 10
                    ActiveCell.EntireRow.Insert
                    count = count + 1
                Wend
                ActiveCell.Offset(8).Select
            End If
            '**INSERT VENDOR NAME**'
            ActiveCell.Value = Worksheets("ASR").Range("GA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            'IF WE ARE SPILLING OVER TO THE NEXT LOCATION, ADD MORE SPACE**'
            If TypeName(ActiveCell.Offset(0, -8).Value) = "Double" Then ActiveCell.EntireRow.Insert
            If ActiveCell.Offset(0, -8).Value = "" Then
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
            End If
            '**ACCOUNT NUMBER**'
            ActiveCell.Value = Worksheets("ASR").Range("GB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            '**UNDERLYING HAULER**'
            If Worksheets("ASR").Range("JL" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("JL" & rowNum).Value
            Range("B" & ActiveCell.Offset(-1).Row).Select
            '**CURRENT CONTRACT EXPIRATION DATE**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 109, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        ActiveCell.Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("F" & ActiveCell.Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
            ActiveCell.Offset(2, 7).Select
        End If
        
        '**INSERT CONTAINER 5 IF EXISTS'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 235, False) <> "" Then
            If TypeName(ActiveCell.Value) = "Double" Then
                ActiveCell.Offset(0, 8).Select
            Else: ActiveCell.Offset(1).Select
            End If
            '**ADD 10 ROWS FOR SPACE'
            If ActiveCell.Offset(0, -8).Value = "" Then
                count = 0
                While count < 10
                    ActiveCell.EntireRow.Insert
                    count = count + 1
                Wend
                ActiveCell.Offset(8).Select
            End If
            '**INSERT VENDOR NAME**'
            ActiveCell.Value = Worksheets("ASR").Range("IA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            'If we are running over into the next location, insert a row'
            If TypeName(ActiveCell.Offset(0, -8).Value) = "Double" Then ActiveCell.EntireRow.Insert
            If ActiveCell.Offset(0, -8).Value = "" Then
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
            End If
            ActiveCell.Value = Worksheets("ASR").Range("IB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            '**UNDERLYING HAULER**'
            If Worksheets("ASR").Range("JL" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("JL" & rowNum).Value
            Range("B" & ActiveCell.Offset(-1).Row).Select
            '**CURRENT CONTRACT EXPIRATION DATE**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 265, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        ActiveCell.Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("F" & ActiveCell.Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
                ActiveCell.Offset(2, 7).Select
        End If
        
        '**INSERT CONTAINER 6/ COMPACTOR 1 IF EXISTS'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 287, False) <> "" Then
            '**COUNT HOW FAR WE REALLY ARE FROM LOC SO WE CAN SEE HOW MUCH TO INSERT
            Set jCell = loc
            Set myCurrent = loc.Offset(3)
            count = 0
            While jCell.Row <> myCurrent.Row
                Set jCell = jCell.Offset(1)
                count = count + 1
            Wend
            jCell.Offset(1).Select
            While count < 60  '**WE WANT TO INSERT IN AT ROW 60**'
                ActiveCell.Offset(1).EntireRow.Insert
                count = count + 1
            Wend
            loc.Offset(50, 8).Select
            
            '**INSERT VENDOR NAME**'
            ActiveCell.Value = Worksheets("ASR").Range("KA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            '**INSERT EXTRA SPACE**
            If TypeName(ActiveCell.Offset(0, -8).Value) = "Double" Then ActiveCell.EntireRow.Insert
            If ActiveCell.Offset(0, -8).Value = "" Then
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
            End If
            ActiveCell.Value = Worksheets("ASR").Range("KB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            '**UNDERLYING HAULER**
            If Worksheets("ASR").Range("LR" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("LR" & rowNum).Value
            Range("B" & ActiveCell.Offset(-1).Row).Select
            'CURRENT CONTRACT EXPIRATION DATE'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 161, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        ActiveCell.Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("F" & ActiveCell.Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
                ActiveCell.Offset(2, 7).Select
        End If
        
        '**INSERT CONTAINER 7 / COMPACTOR 2 IF EXISTS**
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 339, False) <> "" Then
            Set jCell = loc
            Set myCurrent = ActiveCell
            count = 0
            While jCell.Row <> myCurrent.Row
                Set jCell = jCell.Offset(1)
                count = count + 1
            Wend
            jCell.Offset(1).Select
            While count < 80
                ActiveCell.Offset(1).EntireRow.Insert
                count = count + 1
            Wend
            loc.Offset(70, 8).Select
            
            '**INSERT VENDOR NAME'
            ActiveCell.Value = Worksheets("ASR").Range("MA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            '**EXTRA SPACE'
            If TypeName(ActiveCell.Offset(0, -8).Value) = "Double" Then ActiveCell.EntireRow.Insert
            If ActiveCell.Offset(0, -8).Value = "" Then
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
            End If
            
            ActiveCell.Value = Worksheets("ASR").Range("MB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            '**UNDERLYING HAULER**'
            If Worksheets("ASR").Range("NR" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("NR" & rowNum).Value
            Range("B" & ActiveCell.Offset(-1).Row).Select
            '**CURRENT CONTRACT EXPIRATION DATE'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 161, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        ActiveCell.Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("F" & ActiveCell.Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
                ActiveCell.Offset(2, 7).Select
        End If
        
        '**INSERT CONTAINER 8 / COMPACTOR 3
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 391, False) <> "" Then
            If TypeName(ActiveCell.Value) = "Double" Then
                ActiveCell.Offset(0, 8).Select
            Else: ActiveCell.Offset(1).Select
            End If
            'EXTRA SPACE'
            If ActiveCell.Offset(0, -8).Value = "" Then
                count = 0
                While count < 10
                    ActiveCell.EntireRow.Insert
                    count = count + 1
                Wend
                ActiveCell.Offset(8).Select
            End If
            '**INSERT VENDOR NAME'
            ActiveCell.Value = Worksheets("ASR").Range("OA" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            ActiveCell.Offset(1).Select
            'EXTRA SPACE IF NECESSARY
            If TypeName(ActiveCell.Offset(0, -8).Value) = "Double" Then ActiveCell.EntireRow.Insert
            If ActiveCell.Offset(0, -8).Value = "" Then
                ActiveCell.EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
                ActiveCell.Offset(1).EntireRow.Insert
            End If
            ActiveCell.Value = Worksheets("ASR").Range("OB" & rowNum).Value
            ActiveCell.HorizontalAlignment = xlLeft
            If Worksheets("ASR").Range("PR" & rowNum).Value <> "" Then ActiveCell.Offset(1).Value = "Underlying Hauler: " & Worksheets("ASR").Range("PR" & rowNum).Value
            Range("B" & ActiveCell.Offset(-1).Row).Select
            'Insert Contract Expiration Date'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 161, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        ActiveCell.Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 8, False), "mm/dd/yyyy")
                        Range("F" & ActiveCell.Row).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:M1000").Value, 11, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
        End If
        
        If loc.Offset(0, 8).Value = "" And WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:PZ1100"), 287, False) = "" Then loc.Offset(0, 8).Value = "N/A"
    End If
Next
End Sub

Private Sub InsertServices()
'**THIS SUB SAYS INSERT ALL THE SERVICE LEVEL INFORMATION FOR EACH CONTAINER IN EACH LOCATION. THIS GOES ACROSS THE WHOLE SHEET SO SERVICE INFO FOR CURRENT SPEND, BC, RR, SS, ETC. ALL THE WAY TO IMPLEMENTATIONS
'**THIS SUB IS PROBABLY THE MOST IMPORTANT PART, HOLDING ALL THE MEAT INFORMATION


Dim loc As Range
Dim rowNum As Integer
Dim lastRow As Integer
Dim LocID As Range

lastRow = Range("A4000").End(xlUp).Row
For Each loc In Range("A14", "A" & lastRow)
'**SET ALL LOGIC VARIABLES TO FALSE FIRST'
    same = False
    v1 = False: v2 = False: v3 = False: v4 = False: v5 = False: v6 = False: v7 = False: v8 = False
    c1 = False: c2 = False: c3 = False: c4 = False: c5 = False: c6 = False: c7 = False: c8 = False
    r1 = False: r2 = False: r3 = False: r4 = False: r5 = False: r6 = False: r7 = False: r8 = False
    vs1 = False: vs2 = False: vs3 = False: vs4 = False: vs5 = False: vs6 = False: vs7 = False: vs8 = False
    s1 = False: s2 = False: s3 = False: s4 = False: s5 = False: s6 = False: s7 = False: s8 = False

    If TypeName(loc.Value) = "Double" Then
        loc.Select
        '**SALES VOLUME**'
        Range("E" & ActiveCell.Row).Select
        rowNum = WorksheetFunction.Match(loc, Worksheets("INV").Range("locations"), 0) + 7
        ActiveCell.Value = Worksheets("ASR").Range("H" & rowNum).Value
        ActiveCell.Offset(0, 4).Select
        
        '**FIRST CALL VENDOR INFO WHICH BRINGS IN THE REGULAR BASE CHARGE AND VENDOR INFORMATION**'
        '**THEN CALL FEES SUB FOR EACH CONTAINER WHICH BRINGS OVER FUEL/ENV AND FEE TYPES 1-5**'
        Call VendorDiffCont1Svc(rowNum, loc)
        Call FeesContainer1(loc, rowNum)
        Call VendorDiffCont2Svc(rowNum, loc)
        Call FeesContainer2(loc, rowNum)
        Call VendorDiffCont3Svc(rowNum, loc)
        Call FeesContainer3(loc, rowNum)
        Call VendorDiffCont4Svc(rowNum, loc)
        Call FeesContainer4(loc, rowNum)
        Call VendorDiffCont5Svc(rowNum, loc)
        Call FeesContainer5(loc, rowNum)
        Call VendorDiffCont6Svc(rowNum, loc)
        Call FeesContainer6(loc, rowNum)
        Call VendorDiffCont7Svc(rowNum, loc)
        Call FeesContainer7(loc, rowNum)
        Call VendorDiffCont8Svc(rowNum, loc)
        Call FeesContainer8(loc, rowNum)
    End If
Next
End Sub

Private Sub InsertSavings()
'**THIS SUB ENTERS IN THE ACHIEVED SAVINGS COLUMNS THAT SHOW AFTER SS SAVINGS.**'
'**THIS SUB ALSO BRINGS IN RESCINDED, GAINED, AND VARIANCE THAT SHOW AFTER IMPLEMENTED SPEND/SAVINGS**'

Dim lastRow As Integer
Dim aCell As Range
Dim rescinded As Currency
Dim gained As Currency

lastRow = Range("A4000").End(xlUp).Row
    '**BILLING ERRORS MONTHLY SAVINGS**'
    For Each aCell In Range("AJ14", "AJ" & lastRow)
        aCell.Select
        If Range("M" & ActiveCell.Row).Value <> "" And Range("R" & ActiveCell.Row).Value <> "" Then
            '**we have both achieved and current**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - Range("R" & ActiveCell.Row).Value, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value <> "" And Range("R" & ActiveCell.Row).Value = "" Then
            '**we have current, no achieved**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - 0, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value = "" And Range("R" & ActiveCell.Row).Value <> "" Then
            '**we have acheived no current**'
            ActiveCell.Value = Format(0 - Range("R" & ActiveCell.Row).Value, "Currency")
        End If
    Next
    
    
    
    '**RATE REDUCTION MONTHLY SAVINGS**'
    For Each aCell In Range("AK14", "AK" & lastRow)
        aCell.Select
        If Range("M" & ActiveCell.Row).Value <> "" And Range("W" & ActiveCell.Row).Value <> "" Then
            '**we have both achieved and current**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - Range("W" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value <> "" And Range("W" & ActiveCell.Row).Value = "" Then
            '**current, no achieved**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value = "" And Range("W" & ActiveCell.Row).Value <> "" Then
            '**achieved, no current**'
            ActiveCell.Value = Format(0 - Range("W" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value, "Currency")
        End If
    Next
    
    '**VENDOR SWITCH ACHIEVED MONTHLY SAVINGS'
    For Each aCell In Range("AL14", "AL" & lastRow)
        aCell.Select
        If Range("M" & ActiveCell.Row).Value <> "" And Range("AC" & ActiveCell.Row).Value <> "" Then
            '**both achieved and current**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - Range("AC" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value <> "" And Range("AC" & ActiveCell.Row).Value = "" Then
            '**current no achieved**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value = "" And Range("AC" & ActiveCell.Row).Value <> "" Then
            '**achieved no current**'
            ActiveCell.Value = Format(0 - Range("AC" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value, "Currency")
        End If
    Next
    
    '**SERVICE STANDARDIZATION ACHIEVED MONTHLY SAVINGS'
    For Each aCell In Range("AM14", "AM" & lastRow)
        aCell.Select
        If Range("M" & ActiveCell.Row).Value <> "" And Range("AH" & ActiveCell.Row).Value <> "" Then
            '**both achieved and current**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - Range("AH" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value - ActiveCell.Offset(0, -3).Value, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value <> "" And Range("AH" & ActiveCell.Row).Value = "" Then
            '**current no achieved**'
            ActiveCell.Value = Format(Range("M" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value - ActiveCell.Offset(0, -3).Value, "Currency")
        ElseIf Range("M" & ActiveCell.Row).Value = "" And Range("AH" & ActiveCell.Row).Value <> "" Then
            '**achieved no current**'
            ActiveCell.Value = Format(0 - Range("AH" & ActiveCell.Row).Value - ActiveCell.Offset(0, -1).Value - ActiveCell.Offset(0, -2).Value - ActiveCell.Offset(0, -3).Value, "Currency")
        End If
    Next
    
    
    '**TOTAL ACHIEVED MONTHLY SAVINGS
    For Each aCell In Range("AN14", "AN" & lastRow)
        aCell.Select
        '**insert 0s for anything that is missing'
        If aCell.Offset(0, -4).Value <> "" Or aCell.Offset(0, -3).Value <> "" Or aCell.Offset(0, -2).Value <> "" Or aCell.Offset(0, -1).Value <> "" Then
            If aCell.Offset(0, -4).Value = "" Then aCell.Offset(0, -4).Value = Format(0, "Currency")
            If aCell.Offset(0, -3).Value = "" Then aCell.Offset(0, -3).Value = Format(0, "Currency")
            If aCell.Offset(0, -2).Value = "" Then aCell.Offset(0, -2).Value = Format(0, "Currency")
            If aCell.Offset(0, -1).Value = "" Then aCell.Offset(0, -1).Value = Format(0, "Currency")
            aCell.Value = Format(aCell.Offset(0, -4).Value + aCell.Offset(0, -3).Value + aCell.Offset(0, -2).Value + aCell.Offset(0, -1).Value, "Currency")
        '**else label that row for deletion**'
        ElseIf aCell.Offset(0, -4).Value = "" And aCell.Offset(0, -3).Value = "" And aCell.Offset(0, -2).Value = "" And aCell.Offset(0, -1).Value = "" Then
            If aCell.Value <> "x" Then
                If aCell.Offset(-1).Value <> "" Then
                    Range(aCell.Offset(0, 1), aCell.Offset(0, -4)).Borders(xlEdgeTop).Weight = xlThin
                    aCell.Offset(1).Value = "x"
                End If
            Else:
                aCell.Value = ""
                aCell.Offset(1).Value = "x"
            End If
        End If
    Next
    
    
    '**TOTAL ACHIEVED ANNUAL SAVINGS**'
    For Each aCell In Range("AO14", "AO" & lastRow + 1)
        aCell.Select
        '**IF MONTHLY SAVINGS IS NOT EMPTY AND NOT AN X**'
        If aCell.Offset(0, -1).Value <> "" And aCell.Offset(0, -1) <> "x" Then
            aCell.Value = Format(aCell.Offset(0, -1).Value * 12, "Currency")
     
            '**RESCINDED, GAINED, AND VARIANCE SAVINGS
            sumRescinded = (Range("BI" & ActiveCell.Row).Value - Range("AJ" & ActiveCell.Row).Value) + (Range("BJ" & ActiveCell.Row).Value - Range("AK" & ActiveCell.Row).Value) + (Range("BK" & ActiveCell.Row).Value - Range("AL" & ActiveCell.Row).Value) + (Range("BL" & ActiveCell.Row).Value - Range("AM" & ActiveCell.Row).Value) + Range("BO" & ActiveCell.Row).Value
            If sumRescinded < 0 Then
                Range("BP" & ActiveCell.Row).Value = Format(sumRescinded, "Currency")
            Else:
                Range("BP" & ActiveCell.Row).Value = Format(0, "Currency")
            End If
            
            sumGained = Range("AH" & ActiveCell.Row) - Range("AV" & ActiveCell.Row).Value
            If sumGained > 0 Then
                Range("BQ" & ActiveCell.Row).Value = Format(sumGained, "Currency")
            Else
                Range("BQ" & ActiveCell.Row).Value = Format(0, "Currency")
            End If

            '**VARIANCE - TIMS - TAMS**'
            Range("BS" & ActiveCell.Row).Value = Format(Range("BM" & ActiveCell.Row).Value - Range("AN" & ActiveCell.Row).Value, "Currency")
        
        End If
    Next

        
    
End Sub

Private Sub InsertNewContracts()
'**THIS SUB IS TO INSERT IMPLEMENTED CONTRACTS INTO THE END OF THE ASR WORKSHEET**'

Dim loc As Range
Dim contract As String

lastRow = Range("A4000").End(xlUp).Row
For Each loc In Range("A14", "A" & lastRow)
    loc.Select
    If TypeName(loc.Value) = "Double" Then
        rowNum = WorksheetFunction.Match(loc, Worksheets("INV").Range("locations"), 0) + 7
        '**IF WE HAVE AN IMPLEMENTED CONTRACT FOR CONTAINER 1**;
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 1933, False) <> "" Then
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 1978, False)
            If contract <> "" Then
                If Len(contract) > 15 Then
                    Range("BV" & ActiveCell.Row).Select
                    ActiveCell.Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 2, False)
                    ActiveCell.Offset(0, 1).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 4, False), "mm/dd/yyyy")
                    ActiveCell.Offset(0, 2).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 8, False), "mm/dd/yyyy")
                    ActiveCell.Offset(0, 3).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 5, False)
                    ActiveCell.Offset(0, 4).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 12, False)
                    ActiveCell.Offset(0, 5).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 13, False)
                    ActiveCell.Offset(0, 6).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 27, False)
                Else:
                    Range("BV" & ActiveCell.Row).Value = contract
                End If
            End If
        End If
        
        '**CONTAINER 2**'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 1986, False) <> "" Then
            '**IF WE HAVE THE LOCATION ID SELECTED, GO TO OUR SPECIFIED COLUMN AND DOWN 10
            If TypeName(ActiveCell.Value) = "Double" Then
                Range("BV" & ActiveCell.Offset(10).Row).Select
            '**ELSE JUST GO DOWN 10'
            Else: ActiveCell.Offset(10).Select
            End If
            '**INSERT CONTRACT INFO**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 2031, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        Range("BV" & ActiveCell.Row).Select
                        ActiveCell.Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 2, False)
                        ActiveCell.Offset(0, 1).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 4, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 2).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 8, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 3).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 5, False)
                        ActiveCell.Offset(0, 4).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 12, False)
                        ActiveCell.Offset(0, 5).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 13, False)
                        ActiveCell.Offset(0, 6).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 27, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
        End If
        
        '**CONTAINER  3**'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 2041, False) <> "" Then
            If TypeName(ActiveCell.Value) = "Double" Then
                Range("BV" & ActiveCell.Offset(10).Row).Select
            Else: ActiveCell.Offset(10).Select
            End If
            '**INSERT CONTRACT INFO**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 2086, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        Range("BV" & ActiveCell.Row).Select
                        ActiveCell.Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 2, False)
                        ActiveCell.Offset(0, 1).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 4, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 2).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 8, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 3).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 5, False)
                        ActiveCell.Offset(0, 4).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 12, False)
                        ActiveCell.Offset(0, 5).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 13, False)
                        ActiveCell.Offset(0, 6).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 27, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
        End If
        
        '**CONTAINER 4
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 2096, False) <> "" Then
            If TypeName(ActiveCell.Value) = "Double" Then
                Range("BV" & ActiveCell.Offset(10).Row).Select
            Else: ActiveCell.Offset(10).Select
            End If
            '**INSERT CONTRACT INFO**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 2141, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        Range("BV" & ActiveCell.Row).Select
                        ActiveCell.Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 2, False)
                        ActiveCell.Offset(0, 1).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 4, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 2).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 8, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 3).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 5, False)
                        ActiveCell.Offset(0, 4).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 12, False)
                        ActiveCell.Offset(0, 5).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 13, False)
                        ActiveCell.Offset(0, 6).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 27, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
        End If
        
        '**CONTAINER 5**'
        If WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 2151, False) <> "" Then
            If TypeName(ActiveCell.Value) = "Double" Then
                Range("BV" & ActiveCell.Offset(10).Row).Select
            Else: ActiveCell.Offset(10).Select
            End If
            '**INSERT CONTRACT INFO**'
            contract = WorksheetFunction.VLookup(loc, Worksheets("ASR").Range("A8:DDD1099"), 2196, False)
                If contract <> "" Then
                    If Len(contract) > 15 Then
                        Range("BV" & ActiveCell.Row).Select
                        ActiveCell.Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 2, False)
                        ActiveCell.Offset(0, 1).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 4, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 2).Value = Format(WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 8, False), "mm/dd/yyyy")
                        ActiveCell.Offset(0, 3).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 5, False)
                        ActiveCell.Offset(0, 4).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 12, False)
                        ActiveCell.Offset(0, 5).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 13, False)
                        ActiveCell.Offset(0, 6).Value = WorksheetFunction.VLookup(contract, Worksheets("Contract Data").Range("C10:AH200"), 27, False)
                    Else:
                        ActiveCell.Value = contract
                    End If
                End If
        End If
        
    End If
Next

End Sub

Private Sub RemoveSpaceAfterTotals()
'** THIS SUB ALLOWS US TO REMOVE SPACES AFTER WE HAVE ENTERED IN LOCATION OR SUB TOTALS**'

Dim loc As Range
Dim aCell As Range
Dim lastRow As Integer

lastRow = Range("A4000").End(xlUp).Row + 10
For Each loc In Range("A14", "A" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And ActiveCell.Offset(-1).Value = "" And ActiveCell.Offset(0, 9).Value = "" And ActiveCell.Offset(0, 14).Value = "" And ActiveCell.Offset(0, 12).Value = "" Then
        If ActiveCell.Offset(0, 19).Value = "" And ActiveCell.Offset(0, 25).Value = "" And ActiveCell.Offset(0, 30).Value = "" And ActiveCell.Offset(-1, 8).Value = "" Then
            If ActiveCell.Offset(0, 44).Value = "" And ActiveCell.Offset(-1, 9).Value <> "Location Total" And ActiveCell.Offset(0, 30).Value = "" And ActiveCell.Offset(1).Value = "" Then ActiveCell.EntireRow.Delete
        End If
    End If
Next
lastRow = Range("A4000").End(xlUp).Row + 10
For Each loc In Range("A14", "A" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And ActiveCell.Offset(-1).Value = "" And ActiveCell.Offset(0, 9).Value = "" And ActiveCell.Offset(0, 14).Value = "" And ActiveCell.Offset(0, 12).Value = "" Then
        If ActiveCell.Offset(0, 19).Value = "" And ActiveCell.Offset(0, 25).Value = "" And ActiveCell.Offset(0, 30).Value = "" And ActiveCell.Offset(-1, 8).Value = "" Then
            If ActiveCell.Offset(0, 44).Value = "" And ActiveCell.Offset(-1, 9).Value <> "Location Total" And ActiveCell.Offset(0, 30).Value = "" And ActiveCell.Offset(1).Value = "" Then ActiveCell.EntireRow.Delete
        End If
    End If
Next


lastRow = Range("J4000").End(xlUp).Row
'insert row between subtotal and location total'
For Each aCell In Range("J14", "J" & lastRow)
    If aCell.Value = "Subtotal" And aCell.Offset(1).Value = "Location Total" Then
        aCell.Select
        'if our location total is on a row by itself just insert a new row'
        If ActiveCell.Offset(1).Value <> "" And ActiveCell.Offset(1, -9) = "" Then
            ActiveCell.Offset(1).EntireRow.Insert
            Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeBottom).LineStyle = None
            Range(ActiveCell.Offset(0, 5), ActiveCell.Offset(0, 60)).Borders(xlEdgeBottom).LineStyle = None
        'Else if we have an address in the first column and we cannot just insert a new row'
        ElseIf ActiveCell.Offset(1).Value <> "" And ActiveCell.Offset(1, -9) <> "" Then
            ActiveCell.Offset(1).EntireRow.Insert
            Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeBottom).LineStyle = None
            Range(ActiveCell.Offset(0, 5), ActiveCell.Offset(0, 60)).Borders(xlEdgeBottom).LineStyle = None
            ActiveCell.Offset(2, -9).Select
            ActiveCell.Offset(-1).Value = ActiveCell.Value
            ActiveCell.ClearContents
        End If
    End If
Next

lastRow = Range("Z4000").End(xlUp).Row
For Each aCell In Range("Z14", "Z" & lastRow)
    If aCell.Value = "Subtotal" And aCell.Offset(1).Value = "Location Total" Then
        aCell.Select
        'if our location total is on a row by itself just insert a new row'
        If ActiveCell.Offset(1).Value <> "" And ActiveCell.Offset(1, -24) = "" Then
            ActiveCell.Offset(1).EntireRow.Insert
            Range(ActiveCell, ActiveCell.Offset(0, 60)).Borders(xlEdgeBottom).LineStyle = None
            Range(ActiveCell.Offset(0, -16), ActiveCell.Offset(0, -3)).Borders(xlEdgeBottom).LineStyle = None
        'Else if we have an address in the first column and we cannot just insert a new row'
        ElseIf ActiveCell.Offset(1).Value <> "" And ActiveCell.Offset(1, -24) <> "" Then
            ActiveCell.Offset(1).EntireRow.Insert
            Range(ActiveCell, ActiveCell.Offset(0, 60)).Borders(xlEdgeBottom).LineStyle = None
            Range(ActiveCell.Offset(0, -3), ActiveCell.Offset(0, -16)).Borders(xlEdgeBottom).LineStyle = None
            ActiveCell.Offset(2, -24).Select
            ActiveCell.Offset(-1).Value = ActiveCell.Value
            ActiveCell.ClearContents
        End If
    End If
Next

lastRow = Range("AS4000").End(xlUp).Row
For Each aCell In Range("AS14", "AS" & lastRow)
    If aCell.Value = "Subtotal" And aCell.Offset(1).Value = "Location Total" Then
        aCell.Select
        'if our location total is on a row by itself just insert a new row'
        If ActiveCell.Offset(1).Value <> "" And ActiveCell.Offset(1, -44) = "" Then
            ActiveCell.Offset(1).EntireRow.Insert
            Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeBottom).LineStyle = None
            Range(ActiveCell.Offset(0, -4), ActiveCell.Offset(0, -44)).Borders(xlEdgeBottom).LineStyle = None
        'Else if we have an address in the first column and we cannot just insert a new row'
        ElseIf ActiveCell.Offset(1).Value <> "" And ActiveCell.Offset(1, -44) <> "" Then
            ActiveCell.Offset(1).EntireRow.Insert
            Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeBottom).LineStyle = None
            Range(ActiveCell.Offset(0, -4), ActiveCell.Offset(0, -44)).Borders(xlEdgeBottom).LineStyle = None
            ActiveCell.Offset(2, -44).Select
            ActiveCell.Offset(-1).Value = ActiveCell.Value
            ActiveCell.ClearContents
        End If
    End If
Next


End Sub
Private Sub RemoveSpaces()
'**USE THIS SUB WHEN YOU HAVE NOT ENTERED IN ANY TOTALS YET, JUST REMOVING EXTRA UNNECCESSARY BLANK SPACES**'
Dim loc As Range

lastRow = Range("J4000").End(xlUp).Row
For Each loc In Range("A14", "A" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And ActiveCell.Offset(0, 9).Value = "" And ActiveCell.Offset(0, 14).Value = "" And ActiveCell.Offset(-1, 9) = "" Then
        If ActiveCell.Offset(0, 19).Value = "" And ActiveCell.Offset(0, 25).Value = "" And ActiveCell.Offset(0, 30).Value = "" And ActiveCell.Offset(0, 44).Value = "" Then
            If ActiveCell.Offset(-1, 9).Value = "" And ActiveCell.Offset(-1, 25).Value = "" And ActiveCell.Offset(1).Value = "" And ActiveCell.Offset(0, 32).Value = "" And ActiveCell.Offset(1).Value = "" Then ActiveCell.EntireRow.Delete
        End If
    End If
Next
    
End Sub
Private Sub InsertSubtotals()
'Insert Subtotal for each Vendor
Dim loc As Range
Dim total As Currency
Dim totalC As Range
Dim msg As Integer
Dim count As Integer
Dim lastRow As Integer


lastRow = Range("J4000").End(xlUp).Row + 1
'**CURRENT SPEND SUBTOTALS**'
For Each loc In Range("J14", "J" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And (ActiveCell.Offset(-1).Value <> "Subtotal" And ActiveCell.Offset(-1).Value <> "Average Weight Per Haul" And ActiveCell.Offset(-1).Value <> "") Then
        total = 0
        With ActiveCell
            .Value = "Subtotal"
            .Font.Bold = True
            .Font.Italic = True
        End With
        '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
        Set totalC = ActiveCell
        ActiveCell.Offset(-1, 3).Select
        While TypeName(ActiveCell.Value) = "Currency"
            total = total + ActiveCell.Value
            ActiveCell.Offset(-1).Select
        Wend
        totalC.Offset(0, 3).Select
        With ActiveCell
            .Value = total
            .Font.Bold = True
            .Font.Italic = True
            .Borders(xlEdgeTop).Weight = xlThin
        End With
    End If
Next
            
lastRow = Range("O4000").End(xlUp).Row + 1
'**BILLING CORRECTIONS SUBTOTALS**'
For Each loc In Range("O14", "O" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And (ActiveCell.Offset(-1).Value <> "Subtotal" And ActiveCell.Offset(-1).Value <> "Average Weight Per Haul" And ActiveCell.Offset(-1).Value <> "") Then
        total = 0
        With ActiveCell
            .Value = "Subtotal"
            .Font.Bold = True
            .Font.Italic = True
        End With
        '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
        Set totalC = ActiveCell
        ActiveCell.Offset(-1, 3).Select
        While TypeName(ActiveCell.Value) = "Currency"
            total = total + ActiveCell.Value
            ActiveCell.Offset(-1).Select
        Wend
        totalC.Offset(0, 3).Select
        With ActiveCell
            .Value = total
            .Font.Bold = True
            .Font.Italic = True
            .Borders(xlEdgeTop).Weight = xlThin
        End With
    End If
Next
    
lastRow = Range("T4000").End(xlUp).Row + 1
'**RATE REDUCTION SUBTOTALS**'
For Each loc In Range("T14", "T" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And (ActiveCell.Offset(-1).Value <> "Subtotal" And ActiveCell.Offset(-1).Value <> "Average Weight Per Haul" And ActiveCell.Offset(-1).Value <> "") Then
        total = 0
        With ActiveCell
            .Value = "Subtotal"
            .Font.Bold = True
            .Font.Italic = True
        End With
        '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
        Set totalC = ActiveCell
        ActiveCell.Offset(-1, 3).Select
        While TypeName(ActiveCell.Value) = "Currency"
            total = total + ActiveCell.Value
            ActiveCell.Offset(-1).Select
        Wend
        totalC.Offset(0, 3).Select
        With ActiveCell
            .Value = total
            .Font.Bold = True
            .Font.Italic = True
            .Borders(xlEdgeTop).Weight = xlThin
        End With
    End If
Next
             
lastRow = Range("Z4000").End(xlUp).Row + 1
'**VENDOR SWITCH SUBTOTALS**'
For Each loc In Range("Z14", "Z" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And (ActiveCell.Offset(-1).Value <> "Subtotal" And ActiveCell.Offset(-1).Value <> "Average Weight Per Haul" And ActiveCell.Offset(-1).Value <> "") Then
        total = 0
        With ActiveCell
            .Value = "Subtotal"
            .Font.Bold = True
            .Font.Italic = True
        End With
        '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
        Set totalC = ActiveCell
        ActiveCell.Offset(-1, 3).Select
        While TypeName(ActiveCell.Value) = "Currency"
            total = total + ActiveCell.Value
            ActiveCell.Offset(-1).Select
        Wend
        totalC.Offset(0, 3).Select
        With ActiveCell
            .Value = total
            .Font.Bold = True
            .Font.Italic = True
            .Borders(xlEdgeTop).Weight = xlThin
        End With
    End If
Next

lastRow = Range("AE4000").End(xlUp).Row + 1
'**SERVICE STANDARDIZATION SUBTOTALS**'
For Each loc In Range("AE14", "AE" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And (ActiveCell.Offset(-1).Value <> "Subtotal" And ActiveCell.Offset(-1).Value <> "Average Weight Per Haul" And ActiveCell.Offset(-1).Value <> "") Then
        total = 0
        With ActiveCell
            .Value = "Subtotal"
            .Font.Bold = True
            .Font.Italic = True
        End With
        '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
        Set totalC = ActiveCell
        ActiveCell.Offset(-1, 3).Select
        While TypeName(ActiveCell.Value) = "Currency"
            total = total + ActiveCell.Value
            ActiveCell.Offset(-1).Select
        Wend
        totalC.Offset(0, 3).Select
        With ActiveCell
            .Value = total
            .Font.Bold = True
            .Font.Italic = True
            .Borders(xlEdgeTop).Weight = xlThin
        End With
    End If
Next

lastRow = Range("AJ4000").End(xlUp).Row + 1
'**ACHIEVED SAVINGS SUBTOTALS**'
For Each loc In Range("AJ14", "AJ" & lastRow)
    loc.Select
    
    If ActiveCell.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then
        For count = 0 To 5
            total = 0
            Set totalC = ActiveCell
            '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
            ActiveCell.Offset(-1).Select
            While TypeName(ActiveCell.Value) = "Currency"
                total = total + ActiveCell.Value
                ActiveCell.Offset(-1).Select
            Wend
            totalC.Select
            With ActiveCell
                .Value = total
                .Font.Bold = True
                .Font.Italic = True
                .Borders(xlEdgeTop).Weight = xlThin
            End With
            ActiveCell.Offset(0, 1).Select
        Next
        Range("BO" & ActiveCell.Row, "BS" & ActiveCell.Row).Borders(xlEdgeTop).Weight = xlThin
    End If
Next

lastRow = Range("AS4000").End(xlUp).Row + 1
'**IMPLEMENTATION SUBTOTALS**'
For Each loc In Range("AS14", "AS" & lastRow)
    loc.Select
    If ActiveCell.Value = "" And ActiveCell.Offset(-1).Value <> "" And ActiveCell.Offset(-1).Value <> "Average Weight Per Haul" And ActiveCell.Offset(-1).Value <> "Subtotal" Then
        total = 0
        With ActiveCell
            .Value = "Subtotal"
            .Font.Bold = True
            .Font.Italic = True
        End With
        '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
        Set totalC = ActiveCell
        ActiveCell.Offset(-1, 3).Select
        While TypeName(ActiveCell.Value) = "Currency"
            total = total + ActiveCell.Value
            ActiveCell.Offset(-1).Select
        Wend
        totalC.Offset(0, 3).Select
        With ActiveCell
            .Value = total
            .Font.Bold = True
            .Font.Italic = True
            .Borders(xlEdgeTop).Weight = xlThin
        End With
        Range("BI" & ActiveCell.Row, "BM" & ActiveCell.Row).Borders(xlEdgeTop).Weight = xlThin
    End If
Next

lastRow = Range("BI4000").End(xlUp).Row + 1
'**IMPLEMENTED SAVINGS SUBTOTALS**'
For Each loc In Range("BI14", "BI" & lastRow)
    loc.Select
    
    If ActiveCell.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then
        For count = 0 To 4
            total = 0
            Set totalC = ActiveCell
            '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
            ActiveCell.Offset(-1).Select
            While TypeName(ActiveCell.Value) = "Currency"
                total = total + ActiveCell.Value
                ActiveCell.Offset(-1).Select
            Wend
            totalC.Select
            With ActiveCell
                .Value = total
                .Font.Bold = True
                .Font.Italic = True
                .Borders(xlEdgeTop).Weight = xlThin
            End With
            ActiveCell.Offset(0, 1).Select
        Next
    End If
Next

lastRow = Range("BO4000").End(xlUp).Row + 1
'**REJECTED/RESCINDED/GAINED SUBTOTALS**'
For Each loc In Range("BO14", "BO" & lastRow)
    loc.Select
    
    If ActiveCell.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then
        For count = 0 To 4
            total = 0
            Set totalC = ActiveCell
            ActiveCell.Offset(-1).Select
            '**ADD SUM FOR EVERY CURRENCY VALUE ABOVE WHERE OUR TOTAL SHOULD BE**'
            While TypeName(ActiveCell.Value) = "Currency"
                total = total + ActiveCell.Value
                ActiveCell.Offset(-1).Select
            Wend
            totalC.Select
            With ActiveCell
                .Value = total
                .Font.Bold = True
                .Font.Italic = True
                .Borders(xlEdgeTop).Weight = xlThin
            End With
            ActiveCell.Offset(0, 1).Select
        Next
    End If
Next


End Sub

Private Sub InsertLocTotal()
'**THIS SUB ENTERS IN TEH LOCATION TOTAL FOR EACH LOCATION ALL THE WAY ACROSS THE ASR.**'

Dim lastRow As Integer
Dim a_counter As Integer

lastRow = Range("A5000").End(xlUp).Row
Worksheets("ASR Output File").Range("A" & lastRow).Select
If ActiveCell.Offset(30).Value = "" Then
    ActiveCell.Offset(30).Value = 0
Else:
    ActiveCell.Offset(35).Value = 0
End If
Dim loc As Range
Dim rowNum As Integer
Dim locC As Range
Dim marker As Range
Col = RGB(224, 240, 250)
    
lastRow = Range("A4000").End(xlUp).Row
For Each loc In Range("A14", "A" & lastRow)
    loc.Select
    If TypeName(ActiveCell.Value) = "Double" And ActiveCell.Value <> 0 Then
        rowNum = WorksheetFunction.Match(loc, Worksheets("INV").Range("locations"), 0) + 7
        Set locC = ActiveCell
        While TypeName(ActiveCell.Offset(2).Value) <> "Double"
            ActiveCell.Offset(1).Select
        Wend
        
        '**FIND WHERE THE LOCATION ENDS AND THE NEXT LOCATION BEGINS**'
        If ActiveCell.Offset(0, 9).Value = "" And ActiveCell.Offset(0, 14).Value = "" And ActiveCell.Offset(0, 19).Value = "" And ActiveCell.Offset(0, 24).Value = "" And ActiveCell.Offset(0, 30).Value = "" And ActiveCell.Offset(0, 44).Value = "" Then
            Set marker = ActiveCell
        Else:
            ActiveCell.Offset(1).EntireRow.Insert
            ActiveCell.Offset(1).EntireRow.Insert
            ActiveCell.Offset(2).Select
            Set marker = ActiveCell
        End If
        
        '**CURRENT SPEND**'
        marker.Offset(0, 9).Select
        ActiveCell.Value = "Location Total"
        Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeTop).Weight = xlThin
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = Worksheets("ASR").Range("I" & rowNum).Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, -2).Select
        ActiveCell.Value = Worksheets("ASR").Range("L" & rowNum).Value
        ActiveCell.Font.Bold = True
        
        '**BILLING CORRECTIONS**'
        ActiveCell.Offset(0, 4).Select
        ActiveCell.Value = "Location Total"
        Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeTop).Weight = xlThin
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = Worksheets("ASR").Range("N" & rowNum).Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, -2).Select
        ActiveCell.Value = Worksheets("ASR").Range("M" & rowNum).Value
        ActiveCell.Font.Bold = True
        
        '**RATE REDUCTIONS**'
        ActiveCell.Offset(0, 4).Select
        ActiveCell.Value = "Location Total"
        Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeTop).Weight = xlThin
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = Worksheets("ASR").Range("P" & rowNum).Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, -2).Select
        ActiveCell.Value = Worksheets("ASR").Range("O" & rowNum).Value
        ActiveCell.Font.Bold = True
        
        '**VENDOR SWITCH**'
        ActiveCell.Offset(0, 5).Select
        ActiveCell.Value = "Location Total"
        Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeTop).Weight = xlThin
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = Worksheets("ASR").Range("R" & rowNum).Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, -2).Select
        ActiveCell.Value = Worksheets("ASR").Range("Q" & rowNum).Value
        ActiveCell.Font.Bold = True
            
        '**SERVICE STANDARDIZATION**'
        ActiveCell.Offset(0, 4).Select
        ActiveCell.Value = "Location Total"
        Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeTop).Weight = xlThin
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = Worksheets("ASR").Range("T" & rowNum).Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, -2).Select
        ActiveCell.Value = Worksheets("ASR").Range("S" & rowNum).Value
        ActiveCell.Font.Bold = True
                
        '**ACHIEVED SAVINGS**'
        '**8C**'
        ActiveCell.Offset(0, 4).Select
        ActiveCell.Value = Worksheets("ASR").Range("V" & rowNum).Value
        Range(ActiveCell, ActiveCell.Offset(0, 5)).Borders(xlEdgeTop).Weight = xlThin
        Range(ActiveCell, ActiveCell.Offset(0, 5)).Font.Bold = True
        '**RR**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("W" & rowNum).Value
        '**VS**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("X" & rowNum).Value
        '**SS**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("Y" & rowNum).Value
        '**Total**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = ActiveCell.Offset(0, -4).Value + ActiveCell.Offset(0, -3).Value + ActiveCell.Offset(0, -2).Value + ActiveCell.Offset(0, -1).Value
        '**Annual**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = ActiveCell.Offset(0, -1).Value * 12
        
        
        '**IMPLEMENTED SPEND**'
        ActiveCell.Offset(0, 4).Select
        ActiveCell.Value = "Location Total"
        Range(ActiveCell, ActiveCell.Offset(0, 3)).Borders(xlEdgeTop).Weight = xlThin
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, 3).Select
        ActiveCell.Value = Worksheets("ASR").Range("CMH" & rowNum).Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, -2).Select
        ActiveCell.Value = Worksheets("ASR").Range("CMG" & rowNum).Value
        ActiveCell.Font.Bold = True
        
        '**IMPLEMENTED SAVINGS**'
        '**BC**'
        Range("BI" & ActiveCell.Row).Select
        ActiveCell.Value = Worksheets("ASR").Range("CMI" & rowNum).Value
        Range(ActiveCell, ActiveCell.Offset(0, 4)).Borders(xlEdgeTop).Weight = xlThin
        Range(ActiveCell, ActiveCell.Offset(0, 4)).Font.Bold = True
        '**RR**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("CMJ" & rowNum).Value
        '**VS**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("CMK" & rowNum).Value
        '**SS**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("CML" & rowNum).Value
        '**Total**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = ActiveCell.Offset(0, -4).Value + ActiveCell.Offset(0, -3).Value + ActiveCell.Offset(0, -2).Value + ActiveCell.Offset(0, -1).Value
        
        '**REJECTED/RESCINDED/GAINED SAVINGS**'
        Range("BO" & ActiveCell.Row).Select
        '**REJECTED**'
        ActiveCell.Value = Worksheets("ASR").Range("CMB" & rowNum).Value
        Range(ActiveCell, ActiveCell.Offset(0, 4)).Borders(xlEdgeTop).Weight = xlThin
        Range(ActiveCell, ActiveCell.Offset(0, 4)).Font.Bold = True
        '**RESCINDED**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("CMC" & rowNum).Value
        '**GAINED**'
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Worksheets("ASR").Range("CMD" & rowNum).Value
        '**VARIANCE**'
        ActiveCell.Offset(0, 2).Select
        ActiveCell.Value = Worksheets("ASR").Range("CME" & rowNum).Value
        
        If locC.Offset(0, 8).Value = "N/A" Then
            Range(locC.Offset(0, 8), "CD" & marker.Row).Select
            With Selection
                .Interior.Color = RGB(219, 219, 219)
                .ClearContents
                .Borders.LineStyle = xlNone
            End With
            locC.Offset(0, 8).Value = "No Information Provided"
        Else:
            Range(locC, "CD" & marker.Row).Interior.Color = Col
            Call ColorSwitch
        End If
        
    ElseIf ActiveCell.Value = "0" Then
            ActiveCell.ClearContents
            ActiveCell.Offset(2).Select
            'insert the black line and end of file contents'
            Call InsertEndOfFile
    End If
Next
    
End Sub
Private Sub InsertEndOfFile()
    Dim a_counter As Integer
    
    'Insert black line
    Set eof = ActiveCell
    For a_counter = 1 To 81
        Selection.Interior.ColorIndex = 1
        Selection.Font.Color = vbWhite
        'Insert totals for spends
        If a_counter = 13 Then ActiveCell.Value = Worksheets("ASR").Range("I1100").Value
        If a_counter = 18 Then ActiveCell.Value = Worksheets("ASR").Range("N1100").Value
        If a_counter = 23 Then ActiveCell.Value = Worksheets("ASR").Range("P1100").Value
        If a_counter = 29 Then ActiveCell.Value = Worksheets("ASR").Range("R1100").Value
        If a_counter = 34 Then ActiveCell.Value = Worksheets("ASR").Range("T1100").Value
        If a_counter = 36 Then ActiveCell.Value = Worksheets("ASR").Range("V1101").Value
        If a_counter = 37 Then ActiveCell.Value = Worksheets("ASR").Range("W1101").Value
        If a_counter = 38 Then ActiveCell.Value = Worksheets("ASR").Range("X1101").Value
        If a_counter = 39 Then ActiveCell.Value = Worksheets("ASR").Range("Y1101").Value
        If a_counter = 40 Then ActiveCell.Value = Worksheets("ASR").Range("Y1103").Value
        If a_counter = 41 Then ActiveCell.Value = Worksheets("ASR").Range("Y1104").Value
        If a_counter = 48 Then ActiveCell.Value = Worksheets("ASR").Range("CMH1100").Value
        If a_counter = 61 Then ActiveCell.Value = Worksheets("ASR").Range("CMI1100").Value
        If a_counter = 62 Then ActiveCell.Value = Worksheets("ASR").Range("CMJ1100").Value
        If a_counter = 63 Then ActiveCell.Value = Worksheets("ASR").Range("CMK1100").Value
        If a_counter = 64 Then ActiveCell.Value = Worksheets("ASR").Range("CML1100").Value
        If a_counter = 65 Then ActiveCell.Value = Worksheets("ASR").Range("CMM1100").Value
        If a_counter = 67 Then ActiveCell.Value = Worksheets("ASR").Range("CMB1100").Value
        If a_counter = 68 Then ActiveCell.Value = Worksheets("ASR").Range("CMC1100").Value
        If a_counter = 69 Then ActiveCell.Value = Worksheets("ASR").Range("CMD1100").Value
        If a_counter = 71 Then ActiveCell.Value = Worksheets("ASR").Range("CME1100").Value
        ActiveCell.Offset(0, 1).Select
    Next a_counter
    
    '**GO COMPLETE ASR OUTPUT SUMMARY TAB**'
    Worksheets("ASR Output Summary").Activate
    Range("A2").Select
    '**PASTE TOTAL VALUES FROM END OF ASR**'
    Worksheets("ASR").Range("A1102:D1114").Copy
    ActiveCell.PasteSpecial xlPasteAll
    ActiveCell.Offset(17).Select
    '**INSERT EXTERNAL COMMENTS**'
    ActiveCell.Value = "Comments:"
    ActiveCell.Font.Bold = True
    ActiveCell.Offset(1).Select
    Worksheets("ASR").Range("A1117:D1117").Copy
    ActiveCell.PasteSpecial xlPasteAll
    Range("A" & ActiveCell.Row, "D" & ActiveCell.Row).BorderAround _
        ColorIndex:=1, Weight:=xlThick
    '**INSERT CLIENT NEXT ACTION STEPS'
    ActiveCell.Offset(2).Select
    ActiveCell.Value = "Next Action Steps:"
    ActiveCell.Font.Bold = True
    ActiveCell.Offset(1).Select
    'NEXT ACTION STEP 1 IF EXISTS
    If frmComments.txtNextSteps1.Value <> "" Then
        ActiveCell.Value = "1.) " & frmComments.txtNextSteps1.Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(1).Select
    End If
    'INEXT ACTION STEP 2 IF EXISTS
    If frmComments.txtNextSteps2.Value <> "" Then
        ActiveCell.Value = "2.) " & frmComments.txtNextSteps2.Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(1).Select
    End If
    'NEXT ACTION STEP 3 IF EXISTS
    If frmComments.txtNextSteps3.Value <> "" Then
        ActiveCell.Value = "3.) " & frmComments.txtNextSteps3.Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(1).Select
    End If
    'NEXT ACTION STEP 4 IF EXISTS
    If frmComments.txtNextSteps4.Value <> "" Then
        ActiveCell.Value = "4.) " & frmComments.txtNextSteps4.Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(1).Select
    End If
    'NEXT ACTION STEP 5 IF EXISTS
    If frmComments.txtNextSteps5.Value <> "" Then
        ActiveCell.Value = "5.) " & frmComments.txtNextSteps5.Value
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(1).Select
    End If
    ActiveCell.WrapText = True
    
    '**GO BACK TO OUTPUT FILE**'
    Worksheets("ASR Output File").Activate
    
    
End Sub

Private Sub ModifyLocName()
'**THIS SUB ADDS THE LOCATION DBA TO THE LOCATION ID FOR THE FIRST COLUMN AND THE IMPLEMENTED SPEND LOCATION ID COLUMN.**'
Dim loc As Range
Dim rowNum As Integer
Dim marker As Range
Dim lastRow As Integer

lastRow = Worksheets("ASR Output File").Range("A4000").End(xlUp).Row

Worksheets("ASR Output File").Range("A" & lastRow).Select
    For Each loc In Range("A14", "A" & lastRow)
        If TypeName(loc.Value) = "Double" Then
            loc.Select
            rowNum = WorksheetFunction.Match(loc, Worksheets("INV").Range("locations"), 0) + 7
            '**INSERT DBA**'
            ActiveCell.Value = ActiveCell.Value & " - " & Worksheets("INV").Range("B" & rowNum).Value
            '**IF NO INFORMATION, GREY OUT**'
            If loc.Offset(0, 8).Value = "No Information Provided" Then
                Range(loc.Offset(0, 8), "CD" & loc.Offset(2).Row).Select
                Selection.Merge
                With Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
                loc.Offset(3).EntireRow.Delete
                loc.Offset(3).EntireRow.Delete
            Else:
                '**HIGHLIGHT VENDOR CHANGES**'
                If Range("I" & ActiveCell.Row).Value <> Range("Y" & ActiveCell.Row).Value Then Range("Y" & ActiveCell.Row).Interior.ColorIndex = 6
            End If
        End If
    Next
End Sub

Private Sub ColorSwitch()
    'WE ARE ALREADY USING BLUE, SWITCH TO WHITE'
    If Col = RGB(224, 240, 250) Then
            Col = RGB(255, 255, 255)
    Else:
    'WE ARE ALREADY USING WHITE, SWITCH TO BLUE'
        Col = RGB(224, 240, 250)
    End If
End Sub
